﻿// Controllers/AdminController.cs
using WebApplication1.Models;
using System;
using System.Linq;
using System.Web.Mvc;
using WebApplication1.Filters;

using System.Collections.Generic;
using System.Web;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using WebApplication1.Utilities;

namespace WebApplication1.Controllers
{
    [AuthorizeRole("Admin")]

    public class AdminController : BaseController
    {
        private ExamDbContext db = new ExamDbContext();

        public ActionResult Index()
        {
            // 添加调试输出
            System.Diagnostics.Debug.WriteLine("AdminController.Index() 被调用");
            System.Diagnostics.Debug.WriteLine($"Session UserId: {Session["UserId"]}");
            System.Diagnostics.Debug.WriteLine($"Session Role: {Session["Role"]}");

            // 检查用户是否已登录
            if (Session["UserId"] == null)
            {
                System.Diagnostics.Debug.WriteLine("用户未登录，重定向到登录页面");
                return RedirectToAction("Login", "Account");
            }

            // 检查用户是否为管理员
            if (Session["Role"]?.ToString() != "Admin")
            {
                System.Diagnostics.Debug.WriteLine("用户不是管理员，重定向到登录页面");
                return RedirectToAction("Login", "Account");
            }

            System.Diagnostics.Debug.WriteLine("显示管理员首页");
            ViewBag.Title = "管理员首页";
            return View(); // 使用 _AdminLayout.cshtml 布局
        }

        // 考试管理列表
        public ActionResult Exams()
        {
            var exams = db.Exams.ToList();
            return View(exams);
        }
        // Controllers/AdminController.cs

        // 创建考试 GET
        public ActionResult CreateExam()
        {
            // 设置默认合格分数
            var exam = new Exam
            {
                PassScore = 60, // 默认合格分数为60分
                IsActive = true,
                CreatedDate = DateTime.Now
            };
            return View(exam);
        }

        // 创建考试 POST
        [HttpPost]
        public ActionResult CreateExam(Exam exam)
        {
            if (ModelState.IsValid)
            {
                // 验证合格分数范围
                if (exam.PassScore < 0 || exam.PassScore > 100)
                {
                    ModelState.AddModelError("PassScore", "合格分数必须在0-100之间");
                    return View(exam);
                }

                exam.CreatedDate = DateTime.Now;
                db.Exams.Add(exam);
                db.SaveChanges();
                TempData["SuccessMessage"] = "考试创建成功！";
                return RedirectToAction("Exams");
            }
            return View(exam);
        }

        // 编辑考试 GET
        public ActionResult EditExam(int id)
        {
            var exam = db.Exams.Find(id);
            if (exam == null)
            {
                return HttpNotFound();
            }
            return View(exam);
        }

        // 编辑考试 POST
        [HttpPost]
        public ActionResult EditExam(Exam exam)
        {
            if (ModelState.IsValid)
            {
                // 验证合格分数范围
                if (exam.PassScore < 0 || exam.PassScore > 100)
                {
                    ModelState.AddModelError("PassScore", "合格分数必须在0-100之间");
                    return View(exam);
                }

                db.Entry(exam).State = System.Data.Entity.EntityState.Modified;
                db.SaveChanges();
                TempData["SuccessMessage"] = "考试信息更新成功！";
                return RedirectToAction("Exams");
            }
            return View(exam);
        }

        // 删除考试 GET 方法（显示确认页面或不能删除提示）
        public ActionResult DeleteExam(int id)
        {
            var exam = db.Exams
                .Include("Questions")
                .FirstOrDefault(e => e.ExamId == id);

            if (exam == null)
            {
                TempData["ErrorMessage"] = WebApplication1.Utilities.LanguageManager.GetText("ExamNotFound");
                return RedirectToAction("Exams");
            }

            // 检查是否有相关的考试结果
            var hasExamResults = db.ExamResults.Any(er => er.ExamId == id);

            // 检查是否有相关的练习会话
            var hasPracticeSessions = db.PracticeSessions.Any(ps => ps.ExamId == id);

            // 获取翻译文本
            var translations = GetTranslations(
                "DeleteExam", "ExamName", "Description", "QuestionCount",
                "ConfirmDeleteExam", "ConfirmDelete", "Cancel", "Back",
                "CannotDeleteExam", "ExamHasResults", "ExamHasPracticeSessions",
                "DeleteNotAllowed"
            );

            ViewBag.DeleteExam = translations["DeleteExam"];
            ViewBag.ExamName = translations["ExamName"];
            ViewBag.Description = translations["Description"];
            ViewBag.QuestionCount = translations["QuestionCount"];
            ViewBag.ConfirmDeleteExam = translations["ConfirmDeleteExam"];
            ViewBag.ConfirmDelete = translations["ConfirmDelete"];
            ViewBag.Cancel = translations["Cancel"];
            ViewBag.Back = translations["Back"];
            ViewBag.CannotDeleteExam = translations["CannotDeleteExam"];
            ViewBag.ExamHasResults = translations["ExamHasResults"];
            ViewBag.ExamHasPracticeSessions = translations["ExamHasPracticeSessions"];
            ViewBag.DeleteNotAllowed = translations["DeleteNotAllowed"];

            // 如果有相关数据，传递标记到视图
            ViewBag.HasExamResults = hasExamResults;
            ViewBag.HasPracticeSessions = hasPracticeSessions;

            return View(exam);
        }

        // 删除考试 POST 方法（执行删除操作）
        [HttpPost, ActionName("DeleteExam")]
        public ActionResult DeleteExamConfirmed(int id)
        {
            try
            {
                var exam = db.Exams
                    .Include("Questions")
                    .FirstOrDefault(e => e.ExamId == id);

                if (exam == null)
                {
                    TempData["ErrorMessage"] = WebApplication1.Utilities.LanguageManager.GetText("ExamNotFound");
                    return RedirectToAction("Exams");
                }

                // 检查是否有相关的考试结果
                var hasExamResults = db.ExamResults.Any(er => er.ExamId == id);

                // 检查是否有相关的练习会话
                var hasPracticeSessions = db.PracticeSessions.Any(ps => ps.ExamId == id);

                // 如果有相关数据，不能删除
                if (hasExamResults || hasPracticeSessions)
                {
                    TempData["ErrorMessage"] = WebApplication1.Utilities.LanguageManager.GetText("CannotDeleteExamWithRelatedData");
                    return RedirectToAction("Exams");
                }

                // 删除题目选项
                foreach (var question in exam.Questions)
                {
                    var options = db.Options
                        .Where(o => o.QuestionId == question.QuestionId)
                        .ToList();
                    db.Options.RemoveRange(options);
                }

                // 删除题目
                db.Questions.RemoveRange(exam.Questions);

                // 删除考试
                db.Exams.Remove(exam);

                db.SaveChanges();

                TempData["SuccessMessage"] = WebApplication1.Utilities.LanguageManager.GetText("DeleteSuccess");
                return RedirectToAction("Exams");
            }
            catch (Exception ex)
            {
                TempData["ErrorMessage"] = WebApplication1.Utilities.LanguageManager.GetText("DeleteError") + ": " + ex.Message;
                return RedirectToAction("Exams");
            }
        }

        // 试题管理
        public ActionResult Questions(int examId)
        {
            var exam = db.Exams.Find(examId);
            if (exam == null)
            {
                return HttpNotFound();
            }

            ViewBag.ExamName = exam.ExamName;
            var questions = db.Questions.Where(q => q.ExamId == examId&&q.IsActive).ToList();
            ViewBag.ExamId = examId;
            return View(questions);
        }

        // 创建试题
        public ActionResult CreateQuestion(int examId)
        {
            ViewBag.ExamId = examId;
            var question = new Question { ExamId = examId };
            return View(question);
        }

        [HttpPost]
        public ActionResult CreateQuestion(Question question)
        {
            if (ModelState.IsValid)
            {
                db.Questions.Add(question);
                db.SaveChanges();
                return RedirectToAction("Questions", new { examId = question.ExamId });
            }
            ViewBag.ExamId = question.ExamId;
            return View(question);
        }

        // 编辑试题
        public ActionResult EditQuestion(int id)
        {
            var question = db.Questions.Find(id);
            if (question == null)
            {
                return HttpNotFound();
            }
            ViewBag.ExamId = question.ExamId;
            return View(question);
        }

        [HttpPost]
        public ActionResult EditQuestion(Question question)
        {
            if (ModelState.IsValid)
            {
                db.Entry(question).State = System.Data.Entity.EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Questions", new { examId = question.ExamId });
            }
            ViewBag.ExamId = question.ExamId;
            return View(question);
        }


        // 删除试题 GET 方法（显示确认页面）
        public ActionResult DeleteQuestion(int id)
        {
            var question = db.Questions
                .Include("Exam")
                .FirstOrDefault(q => q.QuestionId == id);

            if (question == null)
            {
                TempData["ErrorMessage"] = WebApplication1.Utilities.LanguageManager.GetText("QuestionNotFound");
                return RedirectToAction("Questions", new { examId = ViewBag.ExamId });
            }

            // 检查是否有相关的用户答案记录
            var hasUserAnswers = db.UserAnswers.Any(ua => ua.QuestionId == id);

            // 检查是否有相关的练习答案记录
            var hasPracticeAnswers = db.PracticeAnswers.Any(pa => pa.QuestionId == id);

            // 获取翻译文本
            var translations = GetTranslations(
                "DeleteQuestion", "QuestionContent", "QuestionType", "Score",
                "ConfirmDeleteQuestion", "ConfirmDelete", "Cancel", "Back",
                "CannotDeleteQuestion", "QuestionHasUserAnswers", "QuestionHasPracticeAnswers",
                "DeleteNotAllowed", "ExamName"
            );

            ViewBag.DeleteQuestion = translations["DeleteQuestion"];
            ViewBag.QuestionContent = translations["QuestionContent"];
            ViewBag.QuestionType = translations["QuestionType"];
            ViewBag.Score = translations["Score"];
            ViewBag.ConfirmDeleteQuestion = translations["ConfirmDeleteQuestion"];
            ViewBag.ConfirmDelete = translations["ConfirmDelete"];
            ViewBag.Cancel = translations["Cancel"];
            ViewBag.Back = translations["Back"];
            ViewBag.CannotDeleteQuestion = translations["CannotDeleteQuestion"];
            ViewBag.QuestionHasUserAnswers = translations["QuestionHasUserAnswers"];
            ViewBag.QuestionHasPracticeAnswers = translations["QuestionHasPracticeAnswers"];
            ViewBag.DeleteNotAllowed = translations["DeleteNotAllowed"];
            ViewBag.ExamName = translations["ExamName"];

            // 如果有相关数据，传递标记到视图
            ViewBag.HasUserAnswers = hasUserAnswers;
            ViewBag.HasPracticeAnswers = hasPracticeAnswers;
            ViewBag.ExamId = question.ExamId;

            return View(question);
        }

        // 删除试题 POST 方法（执行删除操作）
        [HttpPost, ActionName("DeleteQuestion")]
        public ActionResult DeleteQuestionConfirmed(int id)
        {
            try
            {
                var question = db.Questions
                    .Include("Exam")
                    .FirstOrDefault(q => q.QuestionId == id);

                if (question == null)
                {
                    TempData["ErrorMessage"] = WebApplication1.Utilities.LanguageManager.GetText("QuestionNotFound");
                    return RedirectToAction("Questions", new { examId = ViewBag.ExamId });
                }

                // 检查是否有相关的用户答案记录
                var hasUserAnswers = db.UserAnswers.Any(ua => ua.QuestionId == id);

                // 检查是否有相关的练习答案记录
                var hasPracticeAnswers = db.PracticeAnswers.Any(pa => pa.QuestionId == id);

                // 删除题目选项
                var options = db.Options
                    .Where(o => o.QuestionId == id)
                    .ToList();
                db.Options.RemoveRange(options);

                // 如果没有用户答案和练习答案，可以安全删除题目
                if (!hasUserAnswers && !hasPracticeAnswers)
                {
                    db.Questions.Remove(question);
                    db.SaveChanges();
                    TempData["SuccessMessage"] = WebApplication1.Utilities.LanguageManager.GetText("DeleteSuccess");
                }
                else
                {
                    // 如果有用户答案或练习答案，软删除题目（标记为已删除但保留记录）
                    question.QuestionText = WebApplication1.Utilities.LanguageManager.GetText("DeletedQuestion");
                    question.IsActive = false; // 假设您在模型中添加了 IsActive 字段
                    db.Entry(question).State = System.Data.Entity.EntityState.Modified;
                    db.SaveChanges();
                    TempData["SuccessMessage"] = WebApplication1.Utilities.LanguageManager.GetText("QuestionMarkedAsDeleted");
                }

                return RedirectToAction("Questions", new { examId = question.ExamId });
            }
            catch (Exception ex)
            {
                TempData["ErrorMessage"] = WebApplication1.Utilities.LanguageManager.GetText("DeleteError") + ": " + ex.Message;
                return RedirectToAction("Questions", new { examId = ViewBag.ExamId });
            }
        }

        // 选项管理
        public ActionResult Options(int questionId)
        {
            var question = db.Questions.Include("Exam").FirstOrDefault(q => q.QuestionId == questionId);
            if (question == null)
            {
                return HttpNotFound();
            }

            ViewBag.QuestionText = question.QuestionText;
            ViewBag.QuestionId = questionId;
            ViewBag.ExamId = question.ExamId; // 添加这一行
            var options = db.Options.Where(o => o.QuestionId == questionId).ToList();
            return View(options);
        }

        // 创建选项
        public ActionResult CreateOption(int questionId)
        {
            ViewBag.QuestionId = questionId;
            var option = new Option { QuestionId = questionId };
            return View(option);
        }

        [HttpPost]
        public ActionResult CreateOption(Option option)
        {
            if (ModelState.IsValid)
            {
                db.Options.Add(option);
                db.SaveChanges();
                return RedirectToAction("Options", new { questionId = option.QuestionId });
            }
            ViewBag.QuestionId = option.QuestionId;
            return View(option);
        }

        // 编辑选项
        public ActionResult EditOption(int id)
        {
            var option = db.Options.Find(id);
            if (option == null)
            {
                return HttpNotFound();
            }
            ViewBag.QuestionId = option.QuestionId;
            return View(option);
        }

        [HttpPost]
        public ActionResult EditOption(Option option)
        {
            if (ModelState.IsValid)
            {
                db.Entry(option).State = System.Data.Entity.EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Options", new { questionId = option.QuestionId });
            }
            ViewBag.QuestionId = option.QuestionId;
            return View(option);
        }

        // 删除选项
        public ActionResult DeleteOption(int id)
        {
            var option = db.Options.Find(id);
            if (option == null)
            {
                return HttpNotFound();
            }
            ViewBag.QuestionId = option.QuestionId;
            return View(option);
        }

        [HttpPost, ActionName("DeleteOption")]
        public ActionResult DeleteOptionConfirmed(int id)
        {
            var option = db.Options.Find(id);
            var questionId = option.QuestionId;
            db.Options.Remove(option);
            db.SaveChanges();
            return RedirectToAction("Options", new { questionId = questionId });
        }

        //// 考试结果统计
        //public ActionResult ExamResults()
        //{
        //    var results = db.ExamResults.Include("User").Include("Exam").ToList();
        //    return View(results);
        //}

        public ActionResult ExamResultDetail(int id)
        {
            var result = db.ExamResults
                .Include("User")
                .Include("Exam")
                .Include("UserAnswers.Question")
                .FirstOrDefault(r => r.ResultId == id);

            if (result == null)
            {
                return HttpNotFound();
            }


            // 批量获取翻译文本
            var translations = GetTranslations(
                "ExamResult", "ExamInfo", "UserName", "StartTime",
                "EndTime", "TimeUsed", "TotalScore", "Score",
                "Percentage", "Pass", "Fail", "NotCompleted",
                "Hours", "Minutes", "Seconds", "Back",
                "Question", "YourAnswer", "CorrectAnswer", "Explanation",
                "SingleChoice", "MultipleChoice", "TrueFalse", "FillBlank",
                "True", "False","TrueValue", "FalseValue"
            );

            // 设置 ViewBag 用于视图中的文本显示
            ViewBag.ExamResult = translations["ExamResult"];
            ViewBag.ExamInfo = translations["ExamInfo"];
            ViewBag.UserName = translations["UserName"];
            ViewBag.StartTime = translations["StartTime"];
            ViewBag.EndTime = translations["EndTime"];
            ViewBag.TimeUsed = translations["TimeUsed"];
            ViewBag.TotalScore = translations["TotalScore"];
            ViewBag.Score = translations["Score"];
            ViewBag.Percentage = translations["Percentage"];
            ViewBag.Pass = translations["Pass"];
            ViewBag.Fail = translations["Fail"];
            ViewBag.NotCompleted = translations["NotCompleted"];
            ViewBag.Hours = translations["Hours"];
            ViewBag.Minutes = translations["Minutes"];
            ViewBag.Seconds = translations["Seconds"];
            ViewBag.Back = translations["Back"];
            ViewBag.Question = translations["Question"];
            ViewBag.YourAnswer = translations["YourAnswer"];
            ViewBag.CorrectAnswer = translations["CorrectAnswer"];
            ViewBag.Explanation = translations["Explanation"];
            ViewBag.SingleChoice = translations["SingleChoice"];
            ViewBag.MultipleChoice = translations["MultipleChoice"];
            ViewBag.TrueFalse = translations["TrueFalse"];
            ViewBag.FillBlank = translations["FillBlank"];
            ViewBag.TrueText = translations["True"];
            ViewBag.FalseText = translations["False"];
            ViewBag.TrueValue = translations["TrueValue"];
            ViewBag.FalseValue = translations["FalseValue"];


            double fenshu = 0;
            foreach (var item in result.UserAnswers)
            {
                fenshu += item.Score;
            }
            ViewBag.fenshu = fenshu;
            return View(result);
        }


        private Dictionary<string, string> GetTranslations(params string[] keys)
        {
            return LanguageManager.GetTexts(keys);
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }



        // 导入试题页面
        public ActionResult ImportQuestions(int examId)
        {
            var exam = db.Exams.Find(examId);
            if (exam == null)
            {
                return HttpNotFound();
            }

            ViewBag.ExamName = exam.ExamName;
            ViewBag.ExamId = examId;
            return View();
        }


        public ActionResult DownloadImportTemplate(int examId)
        {
            try
            {
                // 创建工作簿
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("试题导入模板");

                // 创建表头样式
                ICellStyle headerStyle = workbook.CreateCellStyle();
                IFont headerFont = workbook.CreateFont();
                headerFont.IsBold = true;
                headerFont.FontHeightInPoints = 14;
                headerFont.Color = IndexedColors.White.Index;
                headerStyle.SetFont(headerFont);
                headerStyle.FillForegroundColor = IndexedColors.RoyalBlue.Index;
                headerStyle.FillPattern = FillPattern.SolidForeground;
                headerStyle.BorderTop = BorderStyle.Medium;
                headerStyle.BorderBottom = BorderStyle.Medium;
                headerStyle.BorderLeft = BorderStyle.Medium;
                headerStyle.BorderRight = BorderStyle.Medium;
                headerStyle.VerticalAlignment = VerticalAlignment.Center;
                headerStyle.Alignment = HorizontalAlignment.Center;

                // 创建数据行样式
                ICellStyle dataStyle = workbook.CreateCellStyle();
                dataStyle.BorderTop = BorderStyle.Thin;
                dataStyle.BorderBottom = BorderStyle.Thin;
                dataStyle.BorderLeft = BorderStyle.Thin;
                dataStyle.BorderRight = BorderStyle.Thin;
                dataStyle.VerticalAlignment = VerticalAlignment.Center;
                dataStyle.Alignment = HorizontalAlignment.Left;
                dataStyle.WrapText = true;

                // 创建示例行样式（浅黄色背景）
                ICellStyle exampleStyle = workbook.CreateCellStyle();
                exampleStyle.CloneStyleFrom(dataStyle);
                exampleStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
                exampleStyle.FillPattern = FillPattern.SolidForeground;

                // 创建说明行样式（浅绿色背景）
                ICellStyle infoStyle = workbook.CreateCellStyle();
                infoStyle.CloneStyleFrom(dataStyle);
                infoStyle.FillForegroundColor = IndexedColors.LightGreen.Index;
                infoStyle.FillPattern = FillPattern.SolidForeground;

                // 创建标题行
                IRow headerRow = sheet.CreateRow(0);
                headerRow.HeightInPoints = 30;

                string[] headers = {
            "试题内容", "试题类型", "分数", "正确答案",
            "选项标识1", "选项内容1", "选项标识2", "选项内容2",
            "选项标识3", "选项内容3", "选项标识4", "选项内容4"
        };

                // 填充表头数据并应用样式
                for (int i = 0; i < headers.Length; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(headers[i]);
                    cell.CellStyle = headerStyle;
                }

                // 添加说明行
                IRow infoRow = sheet.CreateRow(1);
                infoRow.HeightInPoints = 25;
                ICell infoCell = infoRow.CreateCell(0);
                infoCell.SetCellValue("说明：请按照此模板格式填写试题数据，试题类型请填写：单选题、多选题、判断题、填空题");
                infoCell.CellStyle = infoStyle;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, headers.Length - 1));

                // 添加示例行
                IRow exampleRow1 = sheet.CreateRow(2);
                exampleRow1.HeightInPoints = 20;
                exampleRow1.CreateCell(0).SetCellValue("中国的首都是哪里？");
                exampleRow1.CreateCell(1).SetCellValue("单选题");
                exampleRow1.CreateCell(2).SetCellValue(10);
                exampleRow1.CreateCell(3).SetCellValue("A");
                exampleRow1.CreateCell(4).SetCellValue("A");
                exampleRow1.CreateCell(5).SetCellValue("北京");
                exampleRow1.CreateCell(6).SetCellValue("B");
                exampleRow1.CreateCell(7).SetCellValue("上海");
                exampleRow1.CreateCell(8).SetCellValue("C");
                exampleRow1.CreateCell(9).SetCellValue("广州");
                exampleRow1.CreateCell(10).SetCellValue("D");
                exampleRow1.CreateCell(11).SetCellValue("深圳");

                IRow exampleRow2 = sheet.CreateRow(3);
                exampleRow2.HeightInPoints = 20;
                exampleRow2.CreateCell(0).SetCellValue("以下哪些是编程语言？");
                exampleRow2.CreateCell(1).SetCellValue("多选题");
                exampleRow2.CreateCell(2).SetCellValue(20);
                exampleRow2.CreateCell(3).SetCellValue("A,B,D");
                exampleRow2.CreateCell(4).SetCellValue("A");
                exampleRow2.CreateCell(5).SetCellValue("C#");
                exampleRow2.CreateCell(6).SetCellValue("B");
                exampleRow2.CreateCell(7).SetCellValue("Java");
                exampleRow2.CreateCell(8).SetCellValue("C");
                exampleRow2.CreateCell(9).SetCellValue("HTML");
                exampleRow2.CreateCell(10).SetCellValue("D");
                exampleRow2.CreateCell(11).SetCellValue("Python");

                IRow exampleRow3 = sheet.CreateRow(4);
                exampleRow3.HeightInPoints = 20;
                exampleRow3.CreateCell(0).SetCellValue("地球是圆的。");
                exampleRow3.CreateCell(1).SetCellValue("判断题");
                exampleRow3.CreateCell(2).SetCellValue(10);
                exampleRow3.CreateCell(3).SetCellValue("正确");
                exampleRow3.CreateCell(4).SetCellValue("");
                exampleRow3.CreateCell(5).SetCellValue("");
                exampleRow3.CreateCell(6).SetCellValue("");
                exampleRow3.CreateCell(7).SetCellValue("");
                exampleRow3.CreateCell(8).SetCellValue("");
                exampleRow3.CreateCell(9).SetCellValue("");
                exampleRow3.CreateCell(10).SetCellValue("");
                exampleRow3.CreateCell(11).SetCellValue("");

                IRow exampleRow4 = sheet.CreateRow(5);
                exampleRow4.HeightInPoints = 25;
                exampleRow4.CreateCell(0).SetCellValue("中国的四大名著是：《______》、《______》、《______》、《______》");
                exampleRow4.CreateCell(1).SetCellValue("填空题");
                exampleRow4.CreateCell(2).SetCellValue(20);
                exampleRow4.CreateCell(3).SetCellValue("红楼梦,西游记,水浒传,三国演义");
                exampleRow4.CreateCell(4).SetCellValue("");
                exampleRow4.CreateCell(5).SetCellValue("");
                exampleRow4.CreateCell(6).SetCellValue("");
                exampleRow4.CreateCell(7).SetCellValue("");
                exampleRow4.CreateCell(8).SetCellValue("");
                exampleRow4.CreateCell(9).SetCellValue("");
                exampleRow4.CreateCell(10).SetCellValue("");
                exampleRow4.CreateCell(11).SetCellValue("");

                IRow exampleRow5 = sheet.CreateRow(6);
                exampleRow5.HeightInPoints = 20;
                exampleRow5.CreateCell(0).SetCellValue("一年有______个月，一周有______天。");
                exampleRow5.CreateCell(1).SetCellValue("填空题");
                exampleRow5.CreateCell(2).SetCellValue(10);
                exampleRow5.CreateCell(3).SetCellValue("12,7");
                exampleRow5.CreateCell(4).SetCellValue("");
                exampleRow5.CreateCell(5).SetCellValue("");
                exampleRow5.CreateCell(6).SetCellValue("");
                exampleRow5.CreateCell(7).SetCellValue("");
                exampleRow5.CreateCell(8).SetCellValue("");
                exampleRow5.CreateCell(9).SetCellValue("");
                exampleRow5.CreateCell(10).SetCellValue("");
                exampleRow5.CreateCell(11).SetCellValue("");

                // 应用示例行样式
                for (int rowIdx = 2; rowIdx <= 6; rowIdx++)
                {
                    IRow row = sheet.GetRow(rowIdx);
                    for (int i = 0; i < headers.Length; i++)
                    {
                        var cell = row.GetCell(i);
                        if (cell != null)
                        {
                            cell.CellStyle = exampleStyle;
                        }
                    }
                }

                // 设置列宽
                int[] columnWidths = {
            45 * 256,  // 试题内容
            15 * 256,  // 试题类型
            10 * 256,  // 分数
            35 * 256,  // 正确答案
            15 * 256,  // 选项标识1
            25 * 256,  // 选项内容1
            15 * 256,  // 选项标识2
            25 * 256,  // 选项内容2
            15 * 256,  // 选项标识3
            25 * 256,  // 选项内容3
            15 * 256,  // 选项标识4
            25 * 256   // 选项内容4
        };

                for (int i = 0; i < columnWidths.Length; i++)
                {
                    sheet.SetColumnWidth(i, columnWidths[i]);
                }

                // 冻结首行
                sheet.CreateFreezePane(0, 2);

                // 将工作簿写入字节数组
                byte[] fileBytes;
                using (var memoryStream = new MemoryStream())
                {
                    workbook.Write(memoryStream);
                    fileBytes = memoryStream.ToArray();
                }

                // 返回文件
                return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "试题导入模板.xlsx");
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("下载模板错误: " + ex.Message);
                return Json(new { success = false, message = "生成模板时发生错误：" + ex.Message }, JsonRequestBehavior.AllowGet);
            }
        }

        // 动态创建模板
        private ActionResult CreateAndDownloadTemplate()
        {
            try
            {
                // 创建工作簿
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("试题导入模板");

                // 创建标题行
                IRow headerRow = sheet.CreateRow(0);
                string[] headers = {
            "试题内容", "试题类型(Single/Multiple/TrueFalse)", "分数", "正确答案",
            "选项标识1", "选项内容1", "选项标识2", "选项内容2",
            "选项标识3", "选项内容3", "选项标识4", "选项内容4"
        };

                for (int i = 0; i < headers.Length; i++)
                {
                    headerRow.CreateCell(i).SetCellValue(headers[i]);
                }

                // 添加示例行
                IRow exampleRow1 = sheet.CreateRow(1);
                exampleRow1.CreateCell(0).SetCellValue("中国的首都是哪里？");
                exampleRow1.CreateCell(1).SetCellValue("Single");
                exampleRow1.CreateCell(2).SetCellValue(10);
                exampleRow1.CreateCell(3).SetCellValue("A");
                exampleRow1.CreateCell(4).SetCellValue("A");
                exampleRow1.CreateCell(5).SetCellValue("北京");
                exampleRow1.CreateCell(6).SetCellValue("B");
                exampleRow1.CreateCell(7).SetCellValue("上海");
                exampleRow1.CreateCell(8).SetCellValue("C");
                exampleRow1.CreateCell(9).SetCellValue("广州");
                exampleRow1.CreateCell(10).SetCellValue("D");
                exampleRow1.CreateCell(11).SetCellValue("深圳");

                // 将工作簿写入字节数组
                byte[] fileBytes;
                using (var memoryStream = new MemoryStream())
                {
                    workbook.Write(memoryStream);
                    fileBytes = memoryStream.ToArray();
                }

                return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "试题导入模板.xlsx");
            }
            catch (Exception ex)
            {
                return Json(new { success = false, message = "创建模板时发生错误：" + ex.Message }, JsonRequestBehavior.AllowGet);
            }
        }


        [HttpPost]
        public ActionResult ImportQuestions(int examId, HttpPostedFileBase excelFile)
        {
            var exam = db.Exams.Find(examId);
            if (exam == null)
            {
                return HttpNotFound();
            }

            if (excelFile == null || excelFile.ContentLength == 0)
            {
                ViewBag.ErrorMessage = "请选择要上传的Excel文件。";
                ViewBag.ExamName = exam.ExamName;
                ViewBag.ExamId = examId;
                return View();
            }

            if (!excelFile.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) &&
                !excelFile.FileName.EndsWith(".xls", StringComparison.OrdinalIgnoreCase))
            {
                ViewBag.ErrorMessage = "请上传.xlsx或.xls格式的Excel文件。";
                ViewBag.ExamName = exam.ExamName;
                ViewBag.ExamId = examId;
                return View();
            }

            try
            {
                List<string> messages = new List<string>();
                int questionCount = 0;
                int optionCount = 0;
                int skippedRows = 0;

                using (var stream = new MemoryStream())
                {
                    excelFile.InputStream.CopyTo(stream);
                    stream.Position = 0;

                    IWorkbook workbook;
                    if (excelFile.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
                    {
                        workbook = new XSSFWorkbook(stream);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(stream);
                    }

                    ISheet sheet = workbook.GetSheetAt(0);
                    if (sheet == null)
                    {
                        ViewBag.ErrorMessage = "Excel文件中没有工作表。";
                        ViewBag.ExamName = exam.ExamName;
                        ViewBag.ExamId = examId;
                        return View();
                    }

                    // 从第7行开始读取数据（跳过标题行、说明行和示例行）
                    int startRow = 7;

                    // 读取数据行
                    for (int row = startRow; row <= sheet.LastRowNum; row++)
                    {
                        IRow dataRow = sheet.GetRow(row);
                        if (dataRow == null) continue;

                        // 检查是否为空行
                        if (IsRowEmpty(dataRow, 12))
                        {
                            skippedRows++;
                            continue;
                        }

                        try
                        {
                            // 读取试题数据
                            string questionText = GetCellValue(dataRow.GetCell(0))?.Trim();
                            string questionTypeText = GetCellValue(dataRow.GetCell(1))?.Trim();
                            string scoreStr = GetCellValue(dataRow.GetCell(2))?.Trim();
                            string correctAnswer = GetCellValue(dataRow.GetCell(3))?.Trim();

                            // 验证必要字段
                            if (string.IsNullOrEmpty(questionText) ||
                                string.IsNullOrEmpty(questionTypeText) ||
                                string.IsNullOrEmpty(scoreStr) ||
                                string.IsNullOrEmpty(correctAnswer))
                            {
                                messages.Add($"第{row + 1}行数据不完整，已跳过。");
                                skippedRows++;
                                continue;
                            }

                            // 将中文试题类型转换为英文标识
                            string questionType = ConvertQuestionType(questionTypeText);
                            if (string.IsNullOrEmpty(questionType))
                            {
                                messages.Add($"第{row + 1}行试题类型无效（{questionTypeText}），已跳过。");
                                skippedRows++;
                                continue;
                            }

                            // 验证分数
                            if (!int.TryParse(scoreStr, out int score) || score <= 0)
                            {
                                messages.Add($"第{row + 1}行分数无效（{scoreStr}），已跳过。");
                                skippedRows++;
                                continue;
                            }

                            // 处理正确答案
                            string finalCorrectAnswer = correctAnswer;
                            if (questionType == "TrueFalse")
                            {
                                if (correctAnswer == "正确")
                                {
                                    finalCorrectAnswer = "True";
                                }
                                else if (correctAnswer == "错误")
                                {
                                    finalCorrectAnswer = "False";
                                }
                            }
                            // 填空题和其它题型的正确答案保持原样

                            // 创建试题
                            var question = new Question
                            {
                                ExamId = examId,
                                QuestionText = questionText,
                                QuestionType = questionType,
                                Score = score,
                                CorrectAnswer = finalCorrectAnswer
                            };

                            db.Questions.Add(question);
                            db.SaveChanges(); // 保存以获取QuestionId
                            questionCount++;

                            // 对于非填空题，读取选项数据
                            if (questionType != "Fill") // 填空题不需要选项
                            {
                                // 读取选项数据（最多4个选项）
                                for (int optionIndex = 0; optionIndex < 4; optionIndex++)
                                {
                                    int optionLabelCol = 4 + (optionIndex * 2);
                                    int optionTextCol = optionLabelCol + 1;

                                    string optionLabel = GetCellValue(dataRow.GetCell(optionLabelCol))?.Trim();
                                    string optionText = GetCellValue(dataRow.GetCell(optionTextCol))?.Trim();

                                    if (!string.IsNullOrEmpty(optionLabel) && !string.IsNullOrEmpty(optionText))
                                    {
                                        var option = new Option
                                        {
                                            QuestionId = question.QuestionId,
                                            OptionLabel = optionLabel,
                                            OptionText = optionText
                                        };

                                        db.Options.Add(option);
                                        optionCount++;
                                    }
                                }
                            }
                            else
                            {
                                // 填空题可以添加提示信息（可选）
                                // 例如：可以在选项列添加填空的提示信息
                            }
                        }
                        catch (Exception rowEx)
                        {
                            messages.Add($"处理第{row + 1}行时发生错误：{rowEx.Message}");
                            skippedRows++;
                        }
                    }
                }

                db.SaveChanges();

                // 导入成功后，使用TempData传递消息并重定向到试题列表页面
                string successMessage = $"导入完成！共导入 {questionCount} 道试题，{optionCount} 个选项。";
                if (skippedRows > 0)
                {
                    successMessage += $"（跳过 {skippedRows} 行）";
                }

                TempData["SuccessMessage"] = successMessage;
                if (messages.Count > 0)
                {
                    TempData["WarningMessage"] = string.Join("<br/>", messages);
                }

                // 重定向到试题列表页面
                return RedirectToAction("Questions", new { examId = examId });
            }
            catch (Exception ex)
            {
                ViewBag.ErrorMessage = "导入过程中发生错误：" + ex.Message;
                ViewBag.ExamName = exam.ExamName;
                ViewBag.ExamId = examId;
                return View();
            }
        }

        // 检查行是否为空
        private bool IsRowEmpty(IRow row, int columnCount)
        {
            if (row == null) return true;

            for (int i = 0; i < columnCount; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null)
                {
                    string cellValue = GetCellValue(cell);
                    if (!string.IsNullOrEmpty(cellValue?.Trim()))
                    {
                        return false;
                    }
                }
            }
            return true;
        }

        // 将中文试题类型转换为英文标识
        private string ConvertQuestionType(string chineseType)
        {
            switch (chineseType)
            {
                case "单选题":
                    return "Single";
                case "多选题":
                    return "Multiple";
                case "判断题":
                    return "TrueFalse";
                case "填空题":
                    return "Fill";
                default:
                    return null;
            }
        }

        // 获取单元格值的辅助方法
        private string GetCellValue(ICell cell)
        {
            if (cell == null) return null;

            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Numeric:
                    return cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Formula:
                    try
                    {
                        return cell.StringCellValue;
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
                default:
                    return cell.ToString();
            }
        }





        // 用户管理列表
        public ActionResult Users()
        {
            var users = db.Users.ToList();
            return View(users);
        }

        // 创建用户
        public ActionResult CreateUser()
        {
            return View();
        }

        [HttpPost]
        public ActionResult CreateUser(User user)
        {
            if (ModelState.IsValid)
            {
                // 检查用户名是否已存在
                var existingUser = db.Users.FirstOrDefault(u => u.Username == user.Username);
                if (existingUser != null)
                {
                    ModelState.AddModelError("Username", "用户名已存在，请选择其他用户名。");
                    return View(user);
                }

                db.Users.Add(user);
                db.SaveChanges();
                TempData["SuccessMessage"] = "用户创建成功！";
                return RedirectToAction("Users");
            }
            return View(user);
        }

        // 编辑用户
        public ActionResult EditUser(int id)
        {
            var user = db.Users.Find(id);
            if (user == null)
            {
                return HttpNotFound();
            }

            // 不要将密码发送到视图
            user.Password = "";
            return View(user);
        }

        [HttpPost]
        public ActionResult EditUser(User user, string confirmPassword)
        {
            if (ModelState.IsValid)
            {
                var existingUser = db.Users.Find(user.UserId);
                if (existingUser == null)
                {
                    return HttpNotFound();
                }

                // 检查用户名是否被其他用户使用
                var userWithSameUsername = db.Users.FirstOrDefault(u => u.Username == user.Username && u.UserId != user.UserId);
                if (userWithSameUsername != null)
                {
                    ModelState.AddModelError("Username", "用户名已存在，请选择其他用户名。");
                    user.Password = ""; // 清空密码字段
                    return View(user);
                }

                // 更新用户信息
                existingUser.Username = user.Username;
                existingUser.FullName = user.FullName;
                existingUser.Role = user.Role;

                // 如果提供了新密码，则更新密码
                if (!string.IsNullOrEmpty(user.Password))
                {
                    if (user.Password != confirmPassword)
                    {
                        ModelState.AddModelError("Password", "密码和确认密码不匹配。");
                        user.Password = ""; // 清空密码字段
                        return View(user);
                    }
                    existingUser.Password = user.Password;
                }

                db.Entry(existingUser).State = System.Data.Entity.EntityState.Modified;
                db.SaveChanges();
                TempData["SuccessMessage"] = "用户信息更新成功！";
                return RedirectToAction("Users");
            }

            user.Password = ""; // 清空密码字段
            return View(user);
        }

        // 删除用户
        public ActionResult DeleteUser(int id)
        {
            var user = db.Users.Find(id);
            if (user == null)
            {
                return HttpNotFound();
            }

            // 防止删除当前登录用户
            if (user.UserId == (int)Session["UserId"])
            {
                TempData["ErrorMessage"] = "不能删除当前登录用户！";
                return RedirectToAction("Users");
            }

            return View(user);
        }

        [HttpPost, ActionName("DeleteUser")]
        public ActionResult DeleteUserConfirmed(int id)
        {
            var user = db.Users.Find(id);
            if (user == null)
            {
                return HttpNotFound();
            }

            // 防止删除当前登录用户
            if (user.UserId == (int)Session["UserId"])
            {
                TempData["ErrorMessage"] = "不能删除当前登录用户！";
                return RedirectToAction("Users");
            }

            // 检查用户是否有相关的考试结果
            var hasExamResults = db.ExamResults.Any(er => er.UserId == id);
            if (hasExamResults)
            {
                TempData["ErrorMessage"] = "该用户有考试记录，无法删除！";
                return RedirectToAction("Users");
            }

            db.Users.Remove(user);
            db.SaveChanges();
            TempData["SuccessMessage"] = "用户删除成功！";
            return RedirectToAction("Users");
        }

        // 重置用户密码
        public ActionResult ResetPassword(int id)
        {
            var user = db.Users.Find(id);
            if (user == null)
            {
                return HttpNotFound();
            }
            return View(user);
        }

        [HttpPost, ActionName("ResetPassword")]
        public ActionResult ResetPasswordConfirmed(int id)
        {
            var user = db.Users.Find(id);
            if (user == null)
            {
                return HttpNotFound();
            }

            // 重置密码为默认密码（可以根据需要修改）
            user.Password = "123456";
            db.Entry(user).State = System.Data.Entity.EntityState.Modified;
            db.SaveChanges();

            TempData["SuccessMessage"] = "用户密码已重置为默认密码！";
            return RedirectToAction("Users");
        }


        // 员工导入页面
        public ActionResult ImportEmployees()
        {
            return View();
        }

        // 下载员工导入模板
        public ActionResult DownloadEmployeeTemplate()
        {
            try
            {
                // 创建工作簿
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("员工导入模板");

                // 创建表头样式
                ICellStyle headerStyle = workbook.CreateCellStyle();
                IFont headerFont = workbook.CreateFont();
                headerFont.IsBold = true;
                headerFont.FontHeightInPoints = 14;
                headerFont.Color = IndexedColors.White.Index;
                headerStyle.SetFont(headerFont);
                headerStyle.FillForegroundColor = IndexedColors.RoyalBlue.Index;
                headerStyle.FillPattern = FillPattern.SolidForeground;
                headerStyle.BorderTop = BorderStyle.Medium;
                headerStyle.BorderBottom = BorderStyle.Medium;
                headerStyle.BorderLeft = BorderStyle.Medium;
                headerStyle.BorderRight = BorderStyle.Medium;
                headerStyle.VerticalAlignment = VerticalAlignment.Center;
                headerStyle.Alignment = HorizontalAlignment.Center;

                // 创建数据行样式
                ICellStyle dataStyle = workbook.CreateCellStyle();
                dataStyle.BorderTop = BorderStyle.Thin;
                dataStyle.BorderBottom = BorderStyle.Thin;
                dataStyle.BorderLeft = BorderStyle.Thin;
                dataStyle.BorderRight = BorderStyle.Thin;
                dataStyle.VerticalAlignment = VerticalAlignment.Center;
                dataStyle.Alignment = HorizontalAlignment.Left;

                // 创建示例行样式（浅黄色背景）
                ICellStyle exampleStyle = workbook.CreateCellStyle();
                exampleStyle.CloneStyleFrom(dataStyle);
                exampleStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
                exampleStyle.FillPattern = FillPattern.SolidForeground;

                // 创建说明行样式（浅绿色背景）
                ICellStyle infoStyle = workbook.CreateCellStyle();
                infoStyle.CloneStyleFrom(dataStyle);
                infoStyle.FillForegroundColor = IndexedColors.LightGreen.Index;
                infoStyle.FillPattern = FillPattern.SolidForeground;

                // 创建标题行
                IRow headerRow = sheet.CreateRow(0);
                headerRow.HeightInPoints = 30;

                string[] headers = { "用户名", "姓名", "角色", "密码" };

                // 填充表头数据并应用样式
                for (int i = 0; i < headers.Length; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(headers[i]);
                    cell.CellStyle = headerStyle;
                }

                // 添加说明行
                IRow infoRow = sheet.CreateRow(1);
                infoRow.HeightInPoints = 25;
                ICell infoCell = infoRow.CreateCell(0);
                infoCell.SetCellValue("说明：请按照此模板格式填写员工数据，角色请填写：Employee");
                infoCell.CellStyle = infoStyle;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, headers.Length - 1));

                // 添加示例行
                IRow exampleRow1 = sheet.CreateRow(2);
                exampleRow1.HeightInPoints = 20;
                exampleRow1.CreateCell(0).SetCellValue("zhangsan");
                exampleRow1.CreateCell(1).SetCellValue("张三");
                exampleRow1.CreateCell(2).SetCellValue("Employee");
                exampleRow1.CreateCell(3).SetCellValue("123456");



                // 应用示例行样式
                for (int rowIdx = 2; rowIdx < 3; rowIdx++)
                {
                    IRow row = sheet.GetRow(rowIdx);
                    for (int i = 0; i < headers.Length; i++)
                    {
                        var cell = row.GetCell(i);
                        if (cell != null)
                        {
                            cell.CellStyle = exampleStyle;
                        }
                    }
                }

                // 设置列宽
                int[] columnWidths = {
            20 * 256,  // 用户名
            20 * 256,  // 姓名
            15 * 256,  // 角色
            20 * 256   // 密码
        };

                for (int i = 0; i < columnWidths.Length; i++)
                {
                    sheet.SetColumnWidth(i, columnWidths[i]);
                }

                // 冻结首行
                sheet.CreateFreezePane(0, 2);

                // 将工作簿写入字节数组
                byte[] fileBytes;
                using (var memoryStream = new MemoryStream())
                {
                    workbook.Write(memoryStream);
                    fileBytes = memoryStream.ToArray();
                }

                // 返回文件
                return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "员工导入模板.xlsx");
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("下载员工模板错误: " + ex.Message);
                TempData["ErrorMessage"] = "生成员工模板时发生错误：" + ex.Message;
                return RedirectToAction("ImportEmployees");
            }
        }

        // 处理员工导入
        [HttpPost]
        public ActionResult ImportEmployees(HttpPostedFileBase excelFile)
        {
            if (excelFile == null || excelFile.ContentLength == 0)
            {
                ViewBag.ErrorMessage = "请选择要上传的Excel文件。";
                return View();
            }

            if (!excelFile.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) &&
                !excelFile.FileName.EndsWith(".xls", StringComparison.OrdinalIgnoreCase))
            {
                ViewBag.ErrorMessage = "请上传.xlsx或.xls格式的Excel文件。";
                return View();
            }

            try
            {
                List<string> messages = new List<string>();
                int employeeCount = 0;
                int skippedRows = 0;

                using (var stream = new MemoryStream())
                {
                    excelFile.InputStream.CopyTo(stream);
                    stream.Position = 0;

                    IWorkbook workbook;
                    if (excelFile.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
                    {
                        workbook = new XSSFWorkbook(stream);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(stream);
                    }

                    ISheet sheet = workbook.GetSheetAt(0);
                    if (sheet == null)
                    {
                        ViewBag.ErrorMessage = "Excel文件中没有工作表。";
                        return View();
                    }

                    // 从第3行开始读取数据（跳过标题行、说明行和示例行）
                    int startRow = 3;

                    // 读取数据行
                    for (int row = startRow; row <= sheet.LastRowNum; row++)
                    {
                        IRow dataRow = sheet.GetRow(row);
                        if (dataRow == null) continue;

                        // 检查是否为空行
                        if (IsEmployeeRowEmpty(dataRow, 4))
                        {
                            skippedRows++;
                            continue;
                        }

                        try
                        {
                            // 读取员工数据
                            string username = GetCellValue(dataRow.GetCell(0))?.Trim();
                            string fullName = GetCellValue(dataRow.GetCell(1))?.Trim();
                            string role = GetCellValue(dataRow.GetCell(2))?.Trim();
                            string password = GetCellValue(dataRow.GetCell(3))?.Trim();

                            // 验证必要字段
                            if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(fullName) ||
                                string.IsNullOrEmpty(role) || string.IsNullOrEmpty(password))
                            {
                                messages.Add($"第{row + 1}行数据不完整，已跳过。");
                                skippedRows++;
                                continue;
                            }

                            // 验证角色
                            if (role != "Employee")
                            {
                                messages.Add($"第{row + 1}行角色无效（{role}），已跳过。");
                                skippedRows++;
                                continue;
                            }

                            // 检查用户名是否已存在
                            var existingUser = db.Users.FirstOrDefault(u => u.Username == username);
                            if (existingUser != null)
                            {
                                messages.Add($"第{row + 1}行用户名 {username} 已存在，已跳过。");
                                skippedRows++;
                                continue;
                            }

                            // 创建员工
                            var user = new User
                            {
                                Username = username,
                                FullName = fullName,
                                Role = role,
                                Password = password
                            };

                            db.Users.Add(user);
                            employeeCount++;
                        }
                        catch (Exception rowEx)
                        {
                            messages.Add($"处理第{row + 1}行时发生错误：{rowEx.Message}");
                            skippedRows++;
                        }
                    }
                }

                db.SaveChanges();

                // 导入成功后，使用TempData传递消息
                string successMessage = $"员工导入完成！共导入 {employeeCount} 名员工。";
                if (skippedRows > 0)
                {
                    successMessage += $"（跳过 {skippedRows} 行）";
                }

                TempData["SuccessMessage"] = successMessage;
                if (messages.Count > 0)
                {
                    TempData["WarningMessage"] = string.Join("<br/>", messages);
                }

                // 重定向到用户管理页面
                return RedirectToAction("Users");
            }
            catch (Exception ex)
            {
                ViewBag.ErrorMessage = "导入员工过程中发生错误：" + ex.Message;
                return View();
            }
        }

        // 检查员工数据行是否为空
        private bool IsEmployeeRowEmpty(IRow row, int columnCount)
        {
            if (row == null) return true;

            for (int i = 0; i < columnCount; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null)
                {
                    string cellValue = GetCellValue(cell);
                    if (!string.IsNullOrEmpty(cellValue?.Trim()))
                    {
                        return false;
                    }
                }
            }
            return true;
        }


 
        public ActionResult ExamResults(DateTime? startDate = null, DateTime? endDate = null, string passStatus = null)
        {
            var query = db.ExamResults.Include("User").Include("Exam").AsQueryable();

            // 日期筛选
            if (startDate.HasValue)
            {
                query = query.Where(r => r.StartTime >= startDate.Value);
            }

            if (endDate.HasValue)
            {
                // 设置结束日期为当天的最后一秒
                var endOfDay = endDate.Value.Date.AddDays(1).AddSeconds(-1);
                query = query.Where(r => r.StartTime <= endOfDay);
            }

            // 合格状态筛选
            if (!string.IsNullOrEmpty(passStatus))
            {
                switch (passStatus)
                {
                    case "passed":
                        query = query.Where(r => r.Score >= r.Exam.PassScore);
                        break;
                    case "failed":
                        query = query.Where(r => r.Score < r.Exam.PassScore);
                        break;
                }
            }

            var results = query.OrderByDescending(r => r.StartTime).ToList();

            // 传递筛选参数到视图
            ViewBag.StartDate = startDate?.ToString("yyyy-MM-dd");
            ViewBag.EndDate = endDate?.ToString("yyyy-MM-dd");
            ViewBag.PassStatus = passStatus;

            return View(results);
        }

        // Controllers/AdminController.cs
        // 更新 ExportExamResultsToExcel 方法中的时长计算部分
        public ActionResult ExportExamResultsToExcel(DateTime? startDate = null, DateTime? endDate = null, string passStatus = null)
        {
            var query = db.ExamResults.Include("User").Include("Exam").AsQueryable();

            // 日期筛选
            if (startDate.HasValue)
            {
                query = query.Where(r => r.StartTime >= startDate.Value);
            }

            if (endDate.HasValue)
            {
                var endOfDay = endDate.Value.Date.AddDays(1).AddSeconds(-1);
                query = query.Where(r => r.StartTime <= endOfDay);
            }

            // 合格状态筛选
            if (!string.IsNullOrEmpty(passStatus))
            {
                switch (passStatus)
                {
                    case "passed":
                        query = query.Where(r => r.Score >= r.Exam.PassScore);
                        break;
                    case "failed":
                        query = query.Where(r => r.Score < r.Exam.PassScore);
                        break;
                }
            }

            var results = query.OrderByDescending(r => r.StartTime).ToList();

            try
            {
                // 创建工作簿
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("考试结果统计");

                // 创建表头样式
                ICellStyle headerStyle = workbook.CreateCellStyle();
                IFont headerFont = workbook.CreateFont();
                headerFont.IsBold = true;
                headerFont.FontHeightInPoints = 12;
                headerFont.Color = IndexedColors.White.Index;
                headerStyle.SetFont(headerFont);
                headerStyle.FillForegroundColor = IndexedColors.RoyalBlue.Index;
                headerStyle.FillPattern = FillPattern.SolidForeground;
                headerStyle.BorderTop = BorderStyle.Medium;
                headerStyle.BorderBottom = BorderStyle.Medium;
                headerStyle.BorderLeft = BorderStyle.Medium;
                headerStyle.BorderRight = BorderStyle.Medium;
                headerStyle.VerticalAlignment = VerticalAlignment.Center;
                headerStyle.Alignment = HorizontalAlignment.Center;

                // 创建数据行样式
                ICellStyle dataStyle = workbook.CreateCellStyle();
                dataStyle.BorderTop = BorderStyle.Thin;
                dataStyle.BorderBottom = BorderStyle.Thin;
                dataStyle.BorderLeft = BorderStyle.Thin;
                dataStyle.BorderRight = BorderStyle.Thin;
                dataStyle.VerticalAlignment = VerticalAlignment.Center;
                dataStyle.Alignment = HorizontalAlignment.Left;

                // 创建标题行
                IRow headerRow = sheet.CreateRow(0);
                headerRow.HeightInPoints = 25;

                string[] headers = {
            "序号", "考生姓名", "考试名称", "考试时间", "得分", "总分", "合格分数", "是否合格", "考试时长(分钟)"
        };

                // 填充表头数据
                for (int i = 0; i < headers.Length; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(headers[i]);
                    cell.CellStyle = headerStyle;
                }

                // 填充数据
                for (int i = 0; i < results.Count; i++)
                {
                    var result = results[i];
                    IRow dataRow = sheet.CreateRow(i + 1);
                    dataRow.HeightInPoints = 20;

                    // 计算考试时长
                    double duration = 0;
                    try
                    {
                        if (result.EndTime != null && result.StartTime != null)
                        {
                            TimeSpan timeDiff = ((DateTime)result.EndTime).Subtract((DateTime)result.StartTime);
                            duration = timeDiff.TotalMinutes;
                        }
                    }
                    catch
                    {
                        duration = 0;
                    }

                    // 是否合格
                    var isPassed = result.Score >= result.Exam.PassScore ? "是" : "否";

                    dataRow.CreateCell(0).SetCellValue(i + 1);
                    dataRow.CreateCell(1).SetCellValue(result.User.FullName);
                    dataRow.CreateCell(2).SetCellValue(result.Exam.ExamName);
                    dataRow.CreateCell(3).SetCellValue(result.StartTime.ToString("yyyy-MM-dd HH:mm:ss"));
                    dataRow.CreateCell(4).SetCellValue(result.Score);
                    dataRow.CreateCell(5).SetCellValue(result.TotalScore);
                    dataRow.CreateCell(6).SetCellValue(result.Exam.PassScore);
                    dataRow.CreateCell(7).SetCellValue(isPassed);
                    dataRow.CreateCell(8).SetCellValue(Math.Round(duration, 2));

                    // 应用数据样式
                    for (int j = 0; j < headers.Length; j++)
                    {
                        dataRow.GetCell(j).CellStyle = dataStyle;
                    }
                }

                // 设置列宽
                for (int i = 0; i < headers.Length; i++)
                {
                    sheet.AutoSizeColumn(i);
                    // 最小列宽
                    if (sheet.GetColumnWidth(i) < 20 * 256)
                    {
                        sheet.SetColumnWidth(i, 20 * 256);
                    }
                }

                // 将工作簿写入字节数组
                byte[] fileBytes;
                using (var memoryStream = new MemoryStream())
                {
                    workbook.Write(memoryStream);
                    fileBytes = memoryStream.ToArray();
                }

                var fileName = $"考试结果统计_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
                return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
            }
            catch (Exception ex)
            {
                TempData["ErrorMessage"] = "导出Excel文件时发生错误：" + ex.Message;
                return RedirectToAction("ExamResults");
            }
        }

       
    }
}